import pymysql


def get_conn():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="0907", db="biyesheji", charset='utf8')
    cursor = conn.cursor()
    sql = "INSERT INTO posts(title,price,class_one,class_two,class_three,picture_url,author,worthy_num,unworthy_num,collect_num,buy_url,content)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    cursor.execute(sql, (13, 13, 13, 1, 1, 1, 1, 1, 1, 1, 1,3))
    conn.commit()
    return cursor


def save(posts=[]):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="0907", db="biyesheji", charset='utf8')
    cursor = conn.cursor()
    sql = "INSERT INTO posts(title,price,class_one,class_two,picture_url,author,worthy_num,unworthy_num,collect_num,buy_url,content,class_three)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    try:
        for Post in posts:
            cursor.execute(sql, (
            Post.title, Post.price, Post.class_one, Post.class_two, Post.picture_url, Post.author,
            Post.worthy_num, Post.unworthy_num, Post.collect_num, Post.buy_url, Post.content,Post.class_three ))
            conn.commit()
            # Post.toString()  ####
            print("sql执行成功2………" + Post.title)  ####
    except Exception as e:
        # 错误回滚
        print(e)
        conn.rollback()
    finally:
        conn.close()

def save_category(dicts=[]):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="0907", db="biyesheji", charset='utf8')
    cursor = conn.cursor()
    sql = "INSERT INTO base_dict(dict_type_code,dict_type1_name,dict_type2_name,dict_type3_name,dict_item2_code,dict_item3_code,dict_sort) VALUES(%s,%s,%s,%s,%s,%s,%s)"
    try:
        for Dict in dicts:
            cursor.execute(sql, (
                Dict.dict_type_code,Dict.dict_type1_name,Dict.dict_type2_name,Dict.dict_type3_name,Dict.dict_item2_code,Dict.dict_item3_code,Dict.dict_sort,))
            conn.commit()
            # Post.toString()  ####
            print("sql执行成功2………" + Dict.dict_type1_name,Dict.dict_type2_name,Dict.dict_type3_name,)  ####
    except Exception as e:
        # 错误回滚
        print(e)
        conn.rollback()
    finally:
        conn.close()

dict_item2_codes=[]
def __init__(self):
    self.dict_item2_codes = []


def find_dict_item2_code():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="0907", db="biyesheji", charset='utf8')
    cursor = conn.cursor()
    num = 0
    try:
        sql = "SELECT DISTINCT dict_item2_code FROM base_dict"
        cursor.execute(sql)
        # print(cursor.rownumber)####
        result = cursor.fetchall()
        for row in result:
            dict_item2_codes.append(str(row[0]))
            print(num,str(row[0]))
            num = num + 1
    except:
        print("Error: unable to fetch data")

    # 关闭数据库连接
    conn.close()
    return dict_item2_codes





